
const mysql = require('mysql2/promise');
const log4js = require('.././reptile/Puppeteer/log/log4js.config');
const logger = log4js.getLogger("MySQL");
// 创建MySQL连接池
const pool = mysql.createPool({
  //   host: 'localhost', // 数据库主机名
  host: 'api.zaidu.in',
  user: 'zaidu', // 用户名
  password: 'x2622011721', // 密码
  database: 'zaidu', // 数据库名
  connectionLimit: 10 // 连接池最大连接数
});


//爬取文章入库
async function connectAndInsert(id, querid, data) {


  try {
    // 获取数据库连接
    const connection = await pool.getConnection();

    // 创建表的 SQL 语句
    const createTableQuery = `
            CREATE TABLE IF NOT EXISTS your_table (
                id INT PRIMARY KEY AUTO_INCREMENT,
                gid VARCHAR(50),
                game VARCHAR(50),
                textContent TEXT,
                texHtml TEXT,
                mediaUrls TEXT,
                originalUrl VARCHAR(255),
                additionalLinks TEXT, -- 添加额外链接字段
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )`;

    // 执行创建表的操作
    await connection.execute(createTableQuery);

    // 判断数据是否更新
    const gamenameQuery = 'SELECT * FROM your_table WHERE game = ?';
    const gamenameParams = [data.game];

    // 执行查询操作，根据 game 字段筛选记录
    const [existingGameRows] = await connection.execute(gamenameQuery, gamenameParams);

    // 获取当前时间
    const currentTime = new Date().toISOString().slice(0, 19).replace("T", " ");

    // 检查查询游戏是否存在
    if (existingGameRows.length > 0) {
      // 如果存在
      const query = 'SELECT * FROM your_table WHERE textContent = ?';
      const params = [data.textContent];

      // 执行查询操作
      const [existingContentRows] = await connection.execute(query, params);

      // 根据游戏检查查询内容结果
      if (existingContentRows.length > 0) {
        console.log('数据已存在，不执行插入操作');
        logger.info('数据库已存在');
        return Promise.resolve('1');

      } else {
        console.log('资讯已更新...正在同步');
        logger.info('资讯已更新...正在同步' + data.originalUrl);

        const insertQuery =
          'INSERT INTO your_table (gid, game, textContent, texHtml, mediaUrls, originalUrl, additionalLinks, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
        const insertParams = [
          data.gid,
          data.game,
          data.textContent,
          data.texHtml,
          Array.isArray(data.mediaUrls) ? data.mediaUrls.join(',') : data.mediaUrls,
          data.originalUrl || '',
          JSON.stringify(data.additionalLinks || []), // 添加额外链接字段并将数组转为字符串
          currentTime
        ];
        // 执行插入操作
        await connection.execute(insertQuery, insertParams);
        console.log('插入数据成功');
        return Promise.resolve('0');

      }
    } else {
      console.log('新游戏');
      if (data.mediaUrls) {
        console.log('图片转换中...');
        console.log(data.mediaUrls)
      }
      const insertQuery =
        'INSERT INTO your_table (gid, game, textContent, texHtml, mediaUrls, originalUrl, additionalLinks, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
      const insertParams = [
        data.gid,
        data.game,
        data.textContent,
        data.texHtml,
        Array.isArray(data.mediaUrls) ? data.mediaUrls.join(',') : data.mediaUrls,
        data.originalUrl || '',
        JSON.stringify(data.additionalLinks || []), // 添加额外链接字段并将数组转为字符串
        currentTime
      ];

      // 执行插入操作
      await connection.execute(insertQuery, insertParams);
      console.log('插入数据成功');

      // 调用 PostArticle 函数
      // PostArticle(id, querid, data.texHtml, Array.isArray(data.imgUrls) ? data.imgUrls.join(',') : data.imgUrls);
      return Promise.resolve('0');

    }

    // 释放数据库连接
    connection.release();

  } catch (error) {
    console.error('操作出错:', error);
    // await browser.close();
  }
}
async function GoogelPlay(gameData) {
  const async = require('async');
  const queue = async.queue(async (gameData, done) => {
    try {
      // 从连接池获取连接
      const connection = await pool.getConnection();
  
      // 查询数据库中是否存在相同 class_name 的数据
      const checkQuery = `
        SELECT * FROM GP_Game_Content
        WHERE class_name = ?`;
  
      const checkParams = [gameData.class_name];
      const [checkRows] = await connection.execute(checkQuery, checkParams);
  
      if (checkRows.length === 0) {
        // 不存在相同的 class_name，执行插入操作
        await insertGameData(connection, gameData);
        logger.info('添加新游'+gameData.class_name);
      } else {
        // 存在相同的 class_name，检查版本号是否有变化
        const existingVersion = checkRows[0].version;
        if (existingVersion !== gameData.version) {
          // if (true) {
          // 版本号有变化，执行更新操作
          await updateGameData(connection, gameData);
          logger.info('进行更新作业..');

        } else {
          console.log('版本号无变化，无需更新');
          logger.info(gameData.class_name+'版本号无变化，无需更新');
        }
      }
  
      // 释放数据库连接
      connection.release();
      
      done(null, '完成'); // 标记处理成功并返回结果
    } catch (error) {
      console.error('Error:', error);
      done(error); // 标记处理失败并返回错误信息
    }
  }, 1); // 并发数为1，即每次只处理一个请求
  
  // 当队列中的任务全部完成时触发
  queue.drain(() => {
    console.log('队列处理完成');
  });
  
  // 将请求添加到队列中
  function addToQueue(gameData) {
    queue.push(gameData, (error, result) => {
      if (error) {
        console.error(`处理请求失败：${error}`);
      } else {
        console.log(`处理请求成功：${result}`);
      }
    });
  }
  
  // 插入游戏数据
  async function insertGameData(connection, gameData) {
    const insertQuery = `
      INSERT INTO GP_Game_Content (
        game_name, star_rating, download_count, age_limit, game_icon,
        game_detail_images, description, version, update_date, android_version_required,
        interactive_elements, release_date, developer, file_size, tags, update_content,
        class_name, download
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  
    const insertParams = [
      gameData.game_name, gameData.star_rating, gameData.download_count, gameData.age_limit,
      gameData.game_icon, gameData.game_detail_images, gameData.description, gameData.version,
      gameData.update_date, gameData.android_version_required, gameData.interactive_elements,
      gameData.release_date, gameData.developer, gameData.file_size, gameData.tags,
      gameData.update_content, gameData.class_name, gameData.download
    ];
  
    // 执行插入操作
    await connection.execute(insertQuery, insertParams);
    console.log('添加游戏成功~');

    logger.info('添加游戏成功~');
    return '添加游戏成功~';
  }
  
  // 更新游戏数据
  async function updateGameData(connection, gameData) {
    const updateQuery = `
      UPDATE GP_Game_Content
      SET
        game_name = ?, star_rating = ?, download_count = ?,
        age_limit = ?, game_icon = ?, game_detail_images = ?,
        description = ?, version = ?, update_date = ?,
        android_version_required = ?, interactive_elements = ?,
        release_date = ?, developer = ?, file_size = ?,
        tags = ?, update_content = ?, download = ?
      WHERE class_name = ?`;
  
    const updateParams = [
      gameData.game_name, gameData.star_rating, gameData.download_count,
      gameData.age_limit, gameData.game_icon, gameData.game_detail_images,
      gameData.description, gameData.version, gameData.update_date,
      gameData.android_version_required, gameData.interactive_elements,
      gameData.release_date, gameData.developer, gameData.file_size,
      gameData.tags, gameData.update_content, gameData.download,
      gameData.class_name
    ];
  
    // 执行更新操作
    await connection.execute(updateQuery, updateParams);
    // console.log('版本号有变化，更新成功');
    logger.info(gameData.class_name+'版本号有变化，更新成功~');
    return '更新成功';
  }
  

  try {
    // 将请求添加到队列中
    addToQueue(gameData);
  } catch (error) {
    console.error('Error:', error);
    logger.error('Error'+error);
  }
}
// 更新icon
async function updateGameIcon(class_name, gameIcon) {
  try {
    // 从连接池获取连接
    const connection = await pool.getConnection();

    // 创建表的 SQL 语句
    const createTableQuery = `
		CREATE TABLE IF NOT EXISTS GP_Game_Content (
		  id INT PRIMARY KEY AUTO_INCREMENT,
		  game_name VARCHAR(255),
		  star_rating DECIMAL(3, 2),
		  download_count VARCHAR(255),
		  age_limit VARCHAR(50),
		  game_icon VARCHAR(255),
		  game_detail_images TEXT,
		  description TEXT,
		  version VARCHAR(50),
		  update_date DATE,
		  android_version_required VARCHAR(50),
		  interactive_elements TEXT,
		  release_date DATE,
		  developer VARCHAR(255),
		  file_size VARCHAR(50),
		  tags TEXT,
		  update_content TEXT,
		  class_name TEXT,
		  download TEXT,
		  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)`;

    // 执行创建表的操作
    await connection.execute(createTableQuery);

    // 查询数据库中是否存在相同 class_name 的数据
    const checkQuery = 'SELECT * FROM GP_Game_Content WHERE class_name = ?';
    const checkParams = [class_name];
    const [checkRows] = await connection.execute(checkQuery, checkParams);

    if (checkRows.length > 0) {
      // 执行更新 game_icon 操作
      const updateQuery = 'UPDATE GP_Game_Content SET game_icon = ? WHERE class_name = ?';
      const updateParams = [gameIcon, class_name];
      await connection.execute(updateQuery, updateParams);
      console.log('已更新');
    } else {
      console.log('不存在该游戏');
    }

    // 释放数据库连接
    connection.release();

    return '完成';
  } catch (error) {
    console.error('Error:', error);
  }
}
// 分页查询
// 分页查询接口
async function getGameContent(page, pageSize) {
  try {
    // 从连接池获取连接
    const connection = await pool.getConnection();

    // 查询总记录数
    const countQuery = 'SELECT COUNT(*) as total FROM GP_Game_Content';
    const [countResult] = await connection.execute(countQuery);
    const total = countResult[0].total;

    // 计算偏移量
    const offset = (page - 1) * pageSize;

    // 分页查询数据
    const query = 'SELECT * FROM GP_Game_Content LIMIT ? OFFSET ?';
    const params = [pageSize, offset];
    const [rows] = await connection.execute(query, params);

    // 释放数据库连接
    connection.release();

    return {
      total,
      data: rows
    };
  } catch (error) {
    console.error('Error:', error);
  }
}
module.exports = {
  pool,
  connectAndInsert,
  GoogelPlay,
  updateGameIcon,
  getGameContent
};